import 'dart:io';

import 'package:Sayagh/Configs.dart';
import 'package:Sayagh/tablepress/goods/Kala.dart';
import 'package:crypto/crypto.dart' as crypto;
import 'package:flutter/foundation.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';

Future<List<List>> csv(
  String url,
  String filename,
  String title, {
  int isActive = 1,
  String description = "-",
  int hasSubTable = 0,
  int hasCategoryColumn = 0,
  int hasSubCategoryColumn = 0,
  int updateDate,
  String jsonData,
  bool isKala = false,
  String cityName = "bandarabas",
}) async {
  var md5 = crypto.md5;
  var client = new HttpClient();
  String fullUrl = "${url}/${TablePressCsvPath}${filename}.csv";
  HttpClientRequest req = await client.getUrl(Uri.parse(fullUrl));
  HttpClientResponse res = await req.close();
  var bytes = await consolidateHttpClientResponseBytes(res);
  var address = (await getApplicationDocumentsDirectory()).path;
  File file = new File(address + "/" + filename);
  await file.writeAsBytes(bytes);
  String csv = await file.readAsString();
  var bytess = await file.readAsBytes();
  String signature = md5.convert(bytess).toString();
  await file.delete();
  List<String> rows = csv.split("\n");
  List<String> head = rows.removeAt(0).split(";");
  List<List<String>> data = new List();
  rows.forEach((row) => data.add(row.split(';')));

  CSVProvider csvProvider = new CSVProvider();
  await csvProvider.open();
  CSV exists = await csvProvider.getCSVbySignature(signature);
  if (exists == null) {
    if (isKala) {
      KalaProvider kalaProvider = new KalaProvider();
      for (int _kalaRow = 0; _kalaRow < data.length; _kalaRow++) {
        Kala kala = new Kala();
        kala = Kala.fromList(data[_kalaRow], title, updateDate, cityName);
        try {
          kalaProvider.insert(kala);
        } catch (e) {
          print("inset error in csv line 53");
          print(e);
        }
        ;
      }
    }
    CSV csv = new CSV();
    csv.signature = signature;
    csv.isActive = isActive;
    csv.title = title;
    csv.fileName = filename;
    csv.url = url;
    csv.description = description;
    csv.hasSubTable = hasSubTable;
    csv.hasCategoryColumn = hasCategoryColumn;
    csv.hasSubCategoryColumn = hasSubCategoryColumn;
    if (updateDate != null) csv.updateDate = updateDate;
    if (jsonData != null) csv.jsonData = jsonData;
    try {
//      await csvProvider.open();
    } catch (e) {
      print("error open db");
      print(e);
    }
    await csvProvider.insert(csv);
//    await csvProvider.close();

  }
//  else
//    await csvProvider.close();

  return [head, data];
}

class CSV {
  int id;
  int isActive;
  String title;
  String fileName;
  String url;
  String description;
  int hasSubTable = 0;
  int hasCategoryColumn;
  int hasSubCategoryColumn = 0;
  int updateDate = DateTime.now().millisecondsSinceEpoch;
  String signature;
  String jsonData;

  CSV();

  CSV.fromMap(Map<String, dynamic> _csv) {
    id = _csv['id'];
    isActive = _csv["isActive"];
    updateDate = _csv['updateDate'];
    title = _csv['title'];
    url = _csv['url'];
    fileName = _csv['fileName'];
    signature = _csv["signature"];
    description = _csv["description"];
    jsonData = _csv["jsonData"];
  }

  Map<String, dynamic> toMap() {
    Map<String, dynamic> _csv = {
      "isActive": isActive,
      "title": title,
      "updateDate": updateDate,
      "url": url,
      "fileName": fileName,
      "signature": signature,
      "description": description,
      "jsonData": jsonData,
    };
    if (id != null) _csv["id"] = id;
    return _csv;
  }
}

class CSVProvider {
  Database db;

  Future open() async {
    db = await openDatabase(ourDatabase, version: 1,
        onOpen: (Database db) async {
      String query = '''
      create table IF NOT EXISTS ${csvTableName}
(
	id INTEGER
		constraint table_name_pk
			primary key autoincrement,
	isActive INTEGER default 1,
	title TEXT not null,
	fileName TEXT not null,
	url TEXT not null,
	description TEXT,
	hasSubTable INTEGER default 0,
	hasCategoryColumn INTEGER default 0,
	hasSubCategoryColumn INTEGER default 0,
	updateDate INTEGER default current_timestamp not null,
	signature TEXT not null,
	jsonData TEXT
);

create unique index table_name_signature_uindex
	on table_name (signature);


         ''';
      await db.execute(query);
    });
  }

  Future close() async {
    db.close();
  }

  Future<CSV> insert(CSV _csv) async {
    _csv.id = await db.insert(csvTableName, _csv.toMap());
    return _csv;
  }

  Future<CSV> getCSVbyId(int id) async {
    List<Map> maps =
        await db.query(csvTableName, where: "id = ?", whereArgs: [id]);
    if (maps.length > 0) return CSV.fromMap(maps.first);
    return null;
  }

  Future<CSV> getCSVbySignature(String signature) async {
//    if (!db.isOpen) await open();
    List<Map> maps = await db
        .query(csvTableName, where: "signature like ?", whereArgs: [signature]);
//    await db.close();
    if (maps.length > 0) return CSV.fromMap(maps.first);
    return null;
  }

  Future<CSV> getByUrlAndFileName(String url, String fileName) async {
    List<Map> maps = await db.query(csvTableName,
        where: "url = ? and fileName=?", whereArgs: [url, fileName]);
    if (maps.length > 0) return CSV.fromMap(maps.first);
    return null;
  }

  Future<int> delete(int id) async {
    return await db.delete(csvTableName, where: "id = ?", whereArgs: [id]);
  }

  Future<int> update(CSV _csv) async {
    return await db.update(csvTableName, _csv.toMap(),
        where: "id = ?", whereArgs: [_csv.id]);
  }
}
